/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package tiebreak.DAL;

import tiebreak.BE.*;
import java.io.IOException;
import java.sql.*;

/**
 *
 * @author kennet
 */
public class MemberDA
{

    Connection m_connection;
    private String dbName = "members";
    private String memberIDField =dbName+ ".memberid";
    private String firstNameField =dbName+ ".firstname";
    private String lastNameField = dbName+".surename";
    private String addressField =dbName+ ".address";
    private String zipcodeField =dbName+ ".zipcode";
    private String cprField = dbName+".cpr";
    private String phoneField =dbName+ ".phone";
    private String emailField = dbName+".email";
    private String typeidField =dbName+ ".type";
    private String isEliteField = dbName+".elitetraining";
    private String underSchoolTrainingField = dbName+".schooltraining";
    private String isCoachField = dbName+".iscoache";

    protected PreparedStatement prepare(boolean b, Member m) throws SQLException, IOException
    {

        PreparedStatement ps;

        if (b)
        {
            ps = m_connection.prepareStatement("UPDATE "+dbName+" SET " + firstNameField + " = ?, "
                    + lastNameField + " = ?, " + addressField + " = ?, " + zipcodeField + " = ?, "
                    + cprField + " = ?, " + phoneField + " = ?, " + emailField + " = ?, "
                    + typeidField + " = ?, " + isEliteField + " = ?, " + underSchoolTrainingField + " = ?, "
                    + isCoachField + " = ? WHERE " + memberIDField + " = ?");
        }
        else
        {

            ps = m_connection.prepareStatement("INSERT INTO "+dbName+" (" + firstNameField
                    + ", " + lastNameField + ", " + addressField + ", " + zipcodeField
                    + ", " + cprField + ", " + phoneField + ", " + emailField + ", "+ typeidField
                    + ", " + isEliteField + ", " + underSchoolTrainingField + ", "
                    + isCoachField + ") VALUES (?,?,?,?,?,?,?,?,?,?,?) ");


        }
        ps.setString(1, m.getFirstName());
        ps.setString(2, m.getLastName());
        ps.setString(3, m.getAddress());
        ps.setInt(4, m.getZipcode());
        ps.setInt(5, m.getCpr());
        ps.setInt(6, m.getPhone());
        ps.setString(7, m.getEmail());
        ps.setInt(8, m.getTypeID());
        ps.setBoolean(9, m.getIsElite());
        ps.setBoolean(10, m.getUnderSchoolTraining());
        ps.setBoolean(11, m.getIsCoach());
        if(b)
            ps.setInt(12, m.getMemberID());
        return ps;
    }

    public MemberDA() throws SQLException
    {
        m_connection = TBConnection.getConnection();
    }

    private Member generate(ResultSet result) throws SQLException
    {
        int memberID = result.getInt(memberIDField);
        String firstName = result.getString(firstNameField);
        String lastName = result.getString(lastNameField);
        String address = result.getString(addressField);
        int zipcode = result.getInt(zipcodeField);
        int cpr = result.getInt(cprField);
        int phone = result.getInt(phoneField);
        String email = result.getString(emailField);
        int typeid = result.getInt(typeidField);
        boolean isElite = result.getBoolean(isEliteField);
        boolean underSchoolTraining = result.getBoolean(underSchoolTrainingField);
        boolean isCoach = result.getBoolean(isCoachField);

        return new Member(memberID, firstName, lastName, address, zipcode, cpr,
                phone, email, typeid, isElite, underSchoolTraining, isCoach);
    }

    public int addMember(Member m) throws IOException, SQLException
    {
        PreparedStatement ps = prepare(false, m);
        ps.executeUpdate();

        ResultSet rs = ps.getGeneratedKeys();
            if (rs.next())
            {
                return rs.getInt(1);
            }
        return 0;
    }

    public void updateMember(Member m) throws IOException, SQLException
    {
        PreparedStatement ps = prepare(true, m);
        ps.executeUpdate();
    }

    public Member getMemberWithCpr(int cpr) throws SQLException
    {
        try
        {
            m_connection = TBConnection.getConnection();
        PreparedStatement ps = m_connection.prepareStatement("SELECT * FROM "+dbName+" WHERE " + cprField + " = ?");
        ps.setInt(1, cpr);

        ResultSet rs = ps.executeQuery();

        if (rs.next())
        {
            return generate(rs);
        }
        }
        catch(NullPointerException e)
        {
            e.printStackTrace();
        }
       
        return null;
    }

    public Member getMemberWithMemberID(int memberID) throws SQLException
    {
        PreparedStatement ps = m_connection.prepareStatement("SELECT * FROM "+dbName+" WHERE " + memberIDField + " = ?");
        ps.setInt(1, memberID);

        ResultSet rs = ps.executeQuery();

        if (rs.next())
        {
            return generate(rs);
        }
        return null;
    }

    public void deleteMember(int seq) throws SQLException
            {
        PreparedStatement ps = m_connection.prepareStatement("DELETE * FROM "+dbName+" WHERE "+memberIDField+" = ?");
        ps.setInt(1, seq);
        ps.executeUpdate();
    }
}
